*******************************************************************************
***
*** Create the consumer credit dataset including borrower and lender characteristics
***
*** Dependencies from folder ${procdata}:
***		- urtepers_panel.dta 
***		- FAMILYtoPNR.dta
***		- familiedata_clean.dta 
***		- firm_panel.dta
***		- kom_2007_reform_link.dta 
***		- macro_data.dta
***		- DK_USD.dta (in ${rawdata})
***
***	Output to folder ${procdata}:
***		- URTEVIRK_URTEPERS_FAM_03-18_temp.dta (intermediate file)
***		- urtepers_panel_ALL.dta 
***
*******************************************************************************

////// Create final dataset
use "${procdata}/urtepers_panel.dta", clear

// Sample selection 
drop if KTOTYPE_KOD==54 | KTOTYPE_KOD==55 | KTOTYPE_KOD==59  //state-guaranteed loans or loans granted by municipality
drop if REST_GAELD_BLB < 0  // Borrower-lender-year pairs with negative debt

*drop loans that we cannot identify as typical bank and nonbank debt
drop if KTOTYPE_KOD<53
drop if KTOTYPE_KOD>57

** Loan delinquincies 
gen byte delinquency = (REST_MARK_KOD != 0) if !mi(REST_MARK_KOD)
clonevar REST_GAELD_BLB_withdelinq = REST_GAELD_BLB
replace REST_GAELD_BLB = . if delinquency == 1

//merge with family identifiers
joinby pnr year using "${procdata}/FAMILYtoPNR.dta", unmatched(both)
tab _merge
keep if _merge==3

//collapse data by family
gcollapse (sum) REST_GAELD_BLB* RNT_BLB (first) KTOTYPE_KOD ///
	(max) REST_MARK_KOD delinquency, by(familie_id cvrnr_lender year)


// Obtain lender information from FIRM
rename cvrnr_lender cvrnr 
merge m:1 cvrnr year using "${procdata}/firm_panel.dta", keepusing(JUR_VIRK_FORM JUR_FRA_DATO JUR_TIL_DATO GF_NACE_DB07)
drop if _merge == 2
generate No_Lender_Data_FIRM=1 if _merge==1
replace No_Lender_Data_FIRM=0 if No_Lender_Data_FIRM==.
drop _merge
label variable No_Lender_Data_FIRM "FIRM data contains no information on lender´s CVR"
rename cvrnr cvrnr_lender
rename JUR_* JUR_*_lender
rename GF_NACE_DB07 GF_NACE_DB07_lender


// Obtain borrower information from Familie data
rename year aar
merge m:1 familie_id aar using "${procdata}\familiedata_clean.dta", keep(match master)
generate No_Borrower_Data_Family_Data=1 if _merge==1
replace No_Borrower_Data_Family_Data=0 if No_Borrower_Data_Family_Data==.
drop _merge
label variable No_Borrower_Data_Family_Data "Family data contains no information on borrowers´s PNR"

*flag lenders
destring GF_NACE_DB07_lender, replace

rename aar year
gsort familie_id year
order year familie_id  cvrnr_lender  No_Lender_Data_FIRM  No_Borrower_Data_Family_Data

* Save temporary, intermediate dataset since code sometimes crashes due to large file size
compress
save "${procdata}/URTEVIRK_URTEPERS_FAM_03-18_temp.dta", replace 


////////////////////////////////////////////////////////////////////////////////
//////////  Part 2 - Sample restrictions, variable generation etc  
////////////////////////////////////////////////////////////////////////////////

*Load relevant variables from household data
use "${procdata}/URTEVIRK_URTEPERS_FAM_03-18_temp.dta", clear

***
*Compute changes in debt 
***

*set panel dimension
egen long id=group(familie_id cvrnr_lender)
xtset id year

*create change in debt
gen lagged_debt=L.REST_GAELD_BLB
replace lagged_debt=0 if lagged_debt==. & year!=2003  //adjust for missing relations in previous years
gen ddebt=REST_GAELD_BLB-lagged_debt
gen log_ddebt=log(ddebt)


***
*Add lender industry codes to distinguish banks vs nonbanks
***
drop if GF_NACE_DB07_lender == . 
rename cvrnr_lender cvrnr
merge m:1 cvrnr year using "${procdata}/firm_panel.dta", keepusing(JUR_HOVED_BRA_DB07) keep(match)

////////////////////////////////////////////
//////////  Sample restrictions   ///////////////
////////////////////////////////////////////

*drop lenders that we are not interested in
destring JUR_HOVED_BRA_DB07, replace
drop if JUR_HOVED_BRA_DB07==649210 //mortgage banks
drop if JUR_HOVED_BRA_DB07>=990000 //extrateritorial entities and unidentified
drop if GF_NACE_DB07_lender>=8400 & GF_NACE_DB07_lender<8500		//government institutions
drop if GF_NACE_DB07_lender==6411   //central bank


***
*Clean municipality codes
***
*adjust the municipality codes back in time to be consistent with the 2007 reform
gen kom_prior=kom if year<2006
drop _merge
joinby kom_prior using "${procdata}/kom_2007_reform_link.dta", unmatched(both)
drop if _merge==2
drop _merge
*generate consistent kommune code variable
gen municipality=newkom if year<2006
replace municipality=kom if year>=2006
*drop entries that have no municipality information
drop if kom==.
drop if municipality==.


***
*Add macro data
***
joinby year using "${procdata}/macro_data.dta"

drop id


///////////////////////////////////////////////////////////////////////////////
// Identify banks and nonbanks 

gen nonbank_lender=0 if GF_NACE_DB07_lender==6419		//banks
replace nonbank_lender=1 if GF_NACE_DB07_lender>=6420  & GF_NACE_DB07_lender<6700	 //nonbanks financial institutions

drop if mi(nonbank_lender) 


///////////////////////////////////////////////////////////////////////////////
// Standardize All Monetary Policy Shocks 

local mpshocks JK_mpshocksign JK_eureon3m_hf AL_OIS3M_mpshock AL_OIS1Y_mpshock  ///
	AL_OIS10Y_mpshock AL_DE10Y_mpshock
	
foreach shock in `mpshocks' {

		qui rename `shock' raw_`shock'
		qui egen `shock' = std(raw_`shock')
		
	}


///////////////////////////////////////////////////////////////////////////////
// Create variables for regressions (numerical ID vars, interest rates, log debt etc.)

egen id_lender = group(cvrnr) // numerical var to identify lenders
egen id_borrower = group(familie_id) // numerical var to identify lenders
egen long id_borrowerlender = group(familie_id cvrnr)

// Compute interest rates
xtset id_borrowerlender year 
gen intrate = RNT_BLB / (0.5* (REST_GAELD_BLB + L.REST_GAELD_BLB))
gstats winsor intrate, cuts(1 98) replace

gen lndebt = log(REST_GAELD_BLB)

// interaction terms: nonbank X macro variables 
gen inter1=nonbank_lender*L.JK_mpshocksign
gen inter2=nonbank_lender*L.DK_real_gdp_grate
gen inter3=nonbank_lender*L.DK_gdp_growth_forecast
gen inter4=nonbank_lender*L.DK_inflation_rate
gen inter5=nonbank_lender*L.vix

// interaction terms with alternative MP shocks 
gen inter1_JKHF=nonbank_lender*L.JK_eureon3m_hf 
gen inter1_AL3M=nonbank_lender*L.AL_OIS3M_mpshock
gen inter1_AL1Y=nonbank_lender*L.AL_OIS1Y_mpshock
gen inter1_AL10Y=nonbank_lender*L.AL_OIS10Y_mpshock
gen inter1_AL10YDE=nonbank_lender*L.AL_DE10Y_mpshock

// Create analog to ILST fixed-effect (based on borrower characteristics)
gquantiles decile_income = famdispindk, xtile n(10) by(year)
gquantiles decile_leverage = fambelaaningsgrad, xtile n(10) by(year)

egen long muni_inc_lev_year_fe=group(municipality decile_income decile_leverage year)

// Industry classification for nonbanks 
destring JUR_HOVED_BRA_DB07, replace
label def lender_type_short ///
641100 "Central Banks" ///
641900 "Commercial Banks" ///
642010 "Financial Holdings" ///
642020 "Non-Financial Holdings" ///
642030 "Mixed Holdings" ///
643010 "Investment Funds" ///
643020 "Money Market Funds" ///
643030 "Investment Companies" ///
643040 "Private Equity & VC" ///
649100 "Financial Leasing" ///
649210 "Mortgage Institutions" ///
649220 "Specialized Finance Companies" ///
649230 "Consumer Credit Companies" ///
649240 "Financial Vehicles" ///
649900 "Investment & Wealth Management" ///
651100 "Life Insurance" ///
651200 "Non-Life Insurance" ///
652000 "Reinsurance" ///
653010 "Pension Funds" ///
653020 "Other Pensions" ///
661100 "Market Administration" ///
661200 "Securities Trading" ///
661900 "Financial Auxiliaries" ///
662100 "Risk Assessment" ///
662200 "Insurance Brokers" ///
662900 "Insurance Auxiliaries" ///
663000 "Fund Management", replace

label values JUR_HOVED_BRA_DB07 lender_type_short

label var famqpassivn "Liabilities, end of year (sum over family and year)"
label var famdispon_13 "Income after tax and interest, rent value off own property added"
label var famforbrug1 "Family consumption"
label var fambelaaningsgrad "Liabilities in percent of total assets"
label var kobtbil "Dummy equal to 1 if bought a new car in this year"
label var famboligvaerdi "Property value"
label var famboligvaerdi_samlet "Property value II"
label var kobtbilvaerdi "Car purchase, value"
label var famnyformue "Total assets, property at market value"


***
*Save data
***

compress 
save "${procdata}/urtepers_panel_ALL.dta", replace








